/// <summary>
// CodeFx.MySqlProviders - .NET Providers for MySql using the framework CodeFx
// Laurent Morisseau
//
// This program is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
// Laurent Morisseau
/// </summary>
/// <author><a href="mailto:laurent.morisseau@gmail.com">Laurent Morisseau</a></author>


namespace CodeFx.MySqlProviders
{
    using System;
    using System.Text;
    using System.Web;
    using System.Data;
    using System.ComponentModel;
    using MySql.Data.MySqlClient;
    using System.Collections;
    using CodeFx;
using System.Collections.Specialized;
    using CodeFx.Web;
    using System.Web.Caching;

    internal static partial class Aspnet_usersDTO
    {
        internal static StringCollection UsersInRoles_FindUsersInRole(uint ApplicationId, uint RoleId, string UserNameToMatch)
        {
            const string command= "SELECT u.UserName FROM aspnet_users u inner join aspnet_usersinroles ur on (u.UserId = ur.UserId AND ?RoleId = ur.RoleId) where u.ApplicationId = ?ApplicationId AND u.UserName LIKE ?UserNameToMatch ORDER BY u.UserName";

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);
            cmd.Parameters.Add(new MySqlParameter("?RoleId", RoleId));
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?UserNameToMatch", UserNameToMatch));
            StringCollection al = null;
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                    al = new StringCollection();
                while (dr.Read())
                {
                    al.Add(dr.GetString(0));
                }
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return al;
        }

        internal static void Update_LastActivityDateWithPersistance(uint userid, string username)
        {
            string Key = string.Concat("Aspnet_users_", username);
            Aspnet_usersDAL dataobject = HttpContext.Current.Items[Key] as Aspnet_usersDAL;
            if (dataobject != null)
            {
                dataobject.LastActivityDate = DateTime.UtcNow;
                HttpContext.Current.Items[Key] = dataobject;
            }
            Update_LastActivityDate(userid);
        }

        private static void Update_LastActivityDate(uint userid)
        {
            MySqlConnection conn = ConnectionString.Current.OpenConnection; 
            MySqlCommand cmd = new MySqlCommand("update aspnet_users set LastActivityDate = ?LastActivityDate where aspnet_users.UserId = ?UserId", conn);
            cmd.Parameters.Add(new MySqlParameter("?LastActivityDate", DateTime.UtcNow));
            cmd.Parameters.Add(new MySqlParameter("?UserId", userid));
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
        private static void Update_LastActivityDate(Aspnet_usersDAL dataObject)
        {
            if (dataObject == null)
                return;

            Update_LastActivityDate(dataObject.UserId);
        }

        [DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
        internal static Aspnet_usersDAL SelectByFk_ApplicationId_UserNameWithPersistance(uint ApplicationId, string UserName)
        {
            string Key = string.Concat("Aspnet_users_",UserName);
            Aspnet_usersDAL dataobject = HttpContext.Current.Items[Key] as Aspnet_usersDAL;
            if (dataobject == null)
            {
                dataobject = SelectByFk_ApplicationId_UserName(ApplicationId, UserName);
                if (dataobject != null)
                    HttpContext.Current.Items[Key]= dataobject;
            }
            return dataobject;

        }

        private static Aspnet_usersDAL SelectByFk_ApplicationId_UserName(uint ApplicationId, string UserName)
        {
            string command = "select UserId,UserName,ApplicationId,MobileAlias,IsAnonymous,LastActivityDate from aspnet_users where ApplicationId =?ApplicationId and Username=?UserName";

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            cmd.Parameters.Add(new MySqlParameter("?UserName", UserName));
            try
            {
                return Select(cmd);
            }
            finally
            {
                conn.Close();
            }
        }

        internal static StringCollection UsersInRoles_GetUsersInRoles(uint ApplicationId, uint RoleId)
        {
            string command = "SELECT u.UserName FROM aspnet_users u, aspnet_usersinroles ur WHERE  u.UserId = ur.UserId AND ?RoleId = ur.RoleId AND u.ApplicationId = ?ApplicationId ORDER BY u.UserName";

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
			MySqlCommand cmd = new MySqlCommand(command, conn);
            cmd.Parameters.Add(new MySqlParameter("?RoleId", RoleId));
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            StringCollection al = null;
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                    al = new StringCollection();
                while (dr.Read())
                {
                    al.Add(dr.GetString(0));
                }
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return al;
        }

        internal static uint GetUserId(string _UserName, uint _ApplicationId)
        {
            string key = string.Concat ("Aspnet_users_Id_",_UserName);
            if (HttpContext.Current.Items[key] != null)
                return (uint)HttpContext.Current.Items[key];
            else
            {
                uint userid = 0;
                string command = "select userid from aspnet_users where UserName =?UserName and ApplicationId =?ApplicationId ";

                MySqlConnection conn = ConnectionString.Current.OpenConnection;       
                MySqlCommand cmd = new MySqlCommand(command, conn);

                cmd.Parameters.Add(new MySqlParameter("?UserName", _UserName));
                cmd.Parameters.Add(new MySqlParameter("?ApplicationId", _ApplicationId));
                MySqlDataReader dr = null;
                try
                {
                    dr = cmd.ExecuteReader();
                    if (dr.Read())
                        userid = dr.GetUInt32(0);
                }
                catch (Exception ex)
                {
                    throw new DALException(cmd.CommandText, ex.InnerException);
                }
                finally
                {
                    if (dr != null)
                        dr.Close();
                    conn.Close();
                }
                HttpContext.Current.Items[key] = userid;
                return userid;
            }
        }
	}
}